Creating the Average Amount per Fine and Year over Year KPIs 11
STEP 1. Average Amt per Fine KPI
STEP 1.1. Create Average Amount per Fine Calculation.
Create Average amount per Fine
Step 1. SumofFinedAmt =
SUM('All Penalties (1)'[Fined Amount])
Step 2. CountofFinedAmt =
COUNT('All Penalties (1)'[Fined Amount])
Step 3. Avg Amt per Fine =
DIVIDE([SumofFinedAmt], [CountofFinedAmt] , 0) -1
Step 1.2. Create the Average Amount per Fine KPI
STEP 2: Create the Year over Year KPI. We will be creating a custom KPI using grouping.
STEP 2.1 Create calculation for YoY KPI
Create YoY
Step 1. TYFinedAmt = SUM('All Penalties (1)'[Fined Amount]) / COUNT('All Penalties (1)'[Fined Amount])
Step 2. LYFinedAmt = CALCULATE([TYFinedAmt], SAMEPERIODLASTYEAR('Date Table'[Date].[Date]))
Step 3. YoY = DIVIDE([TYFinedAmt], [LYFinedAmt], 0) -1
STEP 2.2 Create calculation for Arrow and we are going to use that in our custom KPI. Please follow the gif.
Create Arrow indicator
Arrow = SWITCH(TRUE(),[TYFinedAmt] > [LYFinedAmt], "?", [TYFinedAmt] < [LYFinedAmt], "?", "?")
STEP 2.3. Create YoY KPI. Please follow the gif.
STEP 2.4 Create group for YoY and Arrow. . Please follow the gif.
To make grouping, click YoY and Ctrl key and click arrow.
The idea of grouping in Power BI is very important when it comes to combining and merging two or more values. It helps to group continuous values in to small pieces so users can have a clear view . The link here is a good source to read more about Grouping and Binning In Power BI
Ref : https://blog.pragmaticworks.com/grouping-and-binning-in-power-bi